<?php
App::uses('AppController', 'Controller');
App::uses('Sanitize', 'Utility');
/**
 * Colonnes Controller
 *
 * @property Colonne $Colonnes
 */
class ColonnesController extends AppController {

	//var $components = array('Maconnexion', 'Session');
	public $helpers = array('Date', 'Datefr', 'Moninput', 'Session_gpp');
	
	public $paginate = array(
        'limit' => 25,
        'order' => array(
            'Colonne.topPk' => 'desc','Colonne.id' => 'asc'
        )
    );

    public function ColonnesOfTable($id) {
		$this->myid=$id;
		$this->Colonne->recursive = 0;
		$data = $this->paginate('Colonne',array("Colonne.table_id"=>$id));
		$satable = $this->Colonne->Table->find('first',array('conditions' => array('Table.id' => $id),'recursive'=>-1));
    	$saversion = $this->Colonne->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
    	$sonapp = $this->Colonne->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

		$this->set('sonapp', $sonapp);
		$this->set('saversion', $saversion);
		$this->set('satable', $satable);
		$this->set('colonnes', $data);
		$this->render("index");
	}

/**
 * view method
 *
 * @param string $id
 * @return void
 */
	public function view($id = null) {
		$this->Colonne->id = $id;
		if (!$this->Colonne->exists()) {
			throw new NotFoundException(__('Invalid colonne'));
		}
		$unecolonne = $this->Colonne->find('first',array('conditions' => array('Colonne.id' => $id),'recursive'=>-1));
		$satable = $this->Colonne->Table->find('first',array('conditions' => array('Table.id' => $unecolonne['Colonne']['table_id']),'recursive'=>-1));
		$saversion = $this->Colonne->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
		$sonapp = $this->Colonne->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

		$this->set('satable', $satable);
		$this->set('saversion', $saversion);
		$this->set('sonapp', $sonapp);
		$this->set('colonne', $this->Colonne->read(null, $id));
	}


	public function importColonneOfTable($idtable) {
		$satable = $this->Colonne->Table->find('first',array('conditions' => array('Table.id' => $idtable),'recursive'=>-1));
		$saversion = $this->Colonne->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
		$sonapp = $this->Colonne->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));

		if ($sonapp['Application']['pgdatabase_id']=='1'){
			$req= "SELECT TABLE_SCHEMA AS OWNER, TABLE_NAME AS TABLE_NAME, COLUMN_NAME AS COLUMN_NAME, UPPER( DATA_TYPE ) AS DATA_TYPE, 
						  IFNULL( CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION ) AS DATA_LENGTH, NUMERIC_SCALE AS DATA_PRECISION, 
						  ORDINAL_POSITION AS COLUMN_ID, IS_NULLABLE AS NULLABLE, COLUMN_COMMENT AS COMMENTS, IF( COLUMN_KEY =  'PRI',  '1',  '0' ) AS PRIMARYKEY
					FROM information_schema.COLUMNS
					WHERE UPPER( TABLE_SCHEMA ) = UPPER('".$sonapp['Application']['sid']."') 
					AND UPPER( TABLE_NAME ) = UPPER('".$satable['Table']['name']."') 
					ORDER BY TABLE_NAME, ORDINAL_POSITION";
		}
		if ($sonapp['Application']['pgdatabase_id']=='2'){
			$req=  "SELECT c.owner AS OWNER, c.TABLE_NAME AS TABLE_NAME, c.COLUMN_NAME AS COLUMN_NAME, UPPER(c.DATA_TYPE) AS DATA_TYPE,
					       c.CHAR_LENGTH AS CHAR_LENGTH, c.DATA_LENGTH AS DATA_LENGTH, c.DATA_PRECISION AS DATA_PRECISION, c.COLUMN_ID AS COLUMN_ID, c.NULLABLE AS NULLABLE,
					       co.COMMENTS AS COMMENTS , DECODE(pkc.OWNER, c.owner,'1','0') AS PRIMARYKEY
					  FROM sys.ALL_TAB_COLS c
					 INNER JOIN sys.ALL_COL_COMMENTS co
					    ON co.owner = c.owner
					   AND co.table_name = c.table_name
					   AND co.COLUMN_NAME = c.COLUMN_NAME
					  LEFT OUTER JOIN sys.ALL_CONSTRAINTS pk
					    ON UPPER(pk.owner) = c.owner
					   AND UPPER(pk.table_name) = c.table_name
					   AND pk.constraint_type = 'P'
					  LEFT OUTER JOIN sys.ALL_CONS_COLUMNS pkc
					    ON pkc.owner = pk.owner
					   AND pkc.constraint_name = pk.constraint_name
					   AND pkc.table_name = pk.table_name
					   AND pkc.COLUMN_NAME = c.COLUMN_NAME
					 WHERE UPPER(c.owner) = UPPER('".$sonapp['Application']['login']."')
					   AND UPPER(c.table_name) = UPPER('".$satable['Table']['name']."')
					 ORDER BY c.table_name, c.column_id";
		}

		try{
			$reponse=$this->Maconnexion->dmlquery($sonapp, $req, 1);
			//$this->set('table_distantes', $reponse);
		} catch (Exception $e) {
			$i=0;
		}

		$retour='KO';
		if ($reponse!=null){
			$retour='OK';
			foreach($reponse as $key => $value){
				//debug($value);
				$this->request->data['Colonne']=null;

				$this->request->data['Colonne']['name']=$value['COLUMN_NAME'];

				if ($value['DATA_TYPE']=='VARCHAR2' || $value['DATA_TYPE']=='VARCHAR' || $value['DATA_TYPE']=='CHAR' || $value['DATA_TYPE']=='CHAR()' || $value['DATA_TYPE']=='TINYTEXT'){
					$this->request->data['Colonne']['pgtypecol_id']=1;
					$this->request->data['Colonne']['longueur']=$value['CHAR_LENGTH'];
				}elseif ($value['DATA_TYPE']=='INTEGER' || $value['DATA_TYPE']=='INT' || $value['DATA_TYPE']=='TINYINT' || $value['DATA_TYPE']=='SMALLINT' || (($value['DATA_TYPE']=='LONG' || $value['DATA_TYPE']=='FLOAT' || $value['DATA_TYPE']=='DOUBLE' || $value['DATA_TYPE']=='NUMBER' ) && $value['DATA_PRECISION']==null)) {
					$this->request->data['Colonne']['pgtypecol_id']=2;
				}elseif ($value['DATA_TYPE']=='LONG' || $value['DATA_TYPE']=='FLOAT' || $value['DATA_TYPE']=='DOUBLE' || $value['DATA_TYPE']=='NUMBER' ) {
					$this->request->data['Colonne']['pgtypecol_id']=3;
				}elseif ($value['DATA_TYPE']=='DATE' || $value['DATA_TYPE']=='TIMESTAMP' || $value['DATA_TYPE']=='DATETIME') {
					$this->request->data['Colonne']['pgtypecol_id']=4;
				}else{
					$this->request->data['Colonne']['pgtypecol_id']=5;
				}
				
				if ($value['CHAR_LENGTH']!=null && $value['CHAR_LENGTH']!='NULL' && $value['CHAR_LENGTH']!=0 ){
					$this->request->data['Colonne']['longueur']=$value['CHAR_LENGTH'];
				}elseif ($value['DATA_LENGTH']!=null && $value['DATA_LENGTH']!='NULL' && $value['DATA_LENGTH']!=0 ){
					$this->request->data['Colonne']['longueur']=$value['DATA_LENGTH'];
				}else{
					$this->request->data['Colonne']['longueur']='0';
				}

				if ($value['DATA_PRECISION']!=null && $value['DATA_PRECISION']!='NULL'){
					$this->request->data['Colonne']['longueur']=$this->request->data['Colonne']['longueur'].','.$value['DATA_PRECISION'];
				}

				$this->request->data['Colonne']['topPk']= $value['PRIMARYKEY'];

				$this->request->data['Colonne']['description']= $value['COMMENTS'];

				$this->request->data['Colonne']['table_id']=$idtable;

				//debug($this->request->data['Colonne']);
				$colonne_id=$this->creercolonne($idtable);
				if ($colonne_id==null){
					$retour='KO';
				}
			}
		}

		return $retour;
	}

	private function creercolonne($idtable) {
		//suppression de caractères spéciaux dans le nom de la Colonne
		$this->request->data['Colonne']['name'] = Sanitize::paranoid($this->request->data['Colonne']['name'],array('_')); 
		//Mise en majuscule du nom de la Colonne
		$this->request->data['Colonne']['name'] = strtoupper($this->request->data['Colonne']['name']);

		//$this->request->data = Sanitize::clean($this->request->data, array('encode' => false, 'unicode'=> true));

		$this->Colonne->create();
		if ($this->Colonne->save($this->request->data)) {
			$this->add_colonne_physique($idtable);
			$this->Session->setFlash(__('The colonne has been saved'),'default',array("class"=>'success'));
			return $this->Colonne->id;	
		} else {
			$this->Session->setFlash(__('The colonne could not be saved. Please, try again.'),'default',array("class"=>'error'));
			return null;	
		}
	}
/**
 * add method
 *
 * @return void
 */
	public function add($idtable, $version) {
		//debug($this->request->data);
		if ($this->request->is('post')) {
			if($this->creercolonne($idtable)!=null){
				$this->redirect(array('controller'=>'tables', 'action' => 'view',$idtable));
			}
		}
		$pgtypecols = $this->Colonne->Pgtypecol->find('list');
		$this->set('idtable',$idtable);
		$this->set(compact('pgtypecols'));
	}

	public function add_colonne_physique($idtable){
		$satable=$this->Colonne->Table->find('first',array('conditions' => array('Table.id' => $idtable),'recursive'=>-1));
		$saversion=$this->Colonne->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
		$sonapp = $this->Colonne->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
		
		//si base local alors on crée la table
		if ($sonapp['Application']['hebergement']==0) {	
			if($this->request->data['Colonne']['pgtypecol_id']=='1'){
				$colonne_type='VARCHAR'.'('.$this->request->data['Colonne']['longueur'].')';
			}elseif ($this->request->data['Colonne']['pgtypecol_id']=='2') {
				$colonne_type='INT';
			}elseif ($this->request->data['Colonne']['pgtypecol_id']=='3') {
				if(trim($this->request->data['Colonne']['longueur']) != "") {
					$colonne_type='DECIMAL '.'('.$this->request->data['Colonne']['longueur'].')';
				}else{
					$colonne_type='FLOAT ';
				}
			};

			$colonne_pk=" Gestparamphp_VERSION";
			$colonne_notnull=" ";
	        if($this->request->data['Colonne']['topPk']=='1'){
	        	$colonne_notnull=' NOT NULL ';
	        	
	        	$colonnes = $this->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $satable['Table']['id'],'Colonne.topPk =' => '1'),'recursive'=>-1 ));
	        	
	        	foreach ($colonnes as $colonne):
	        		$colonne_pk = $colonne_pk.",".$colonne['Colonne']['name'];
				endforeach;

	        };

	        //$this->Maconnexion->changer($saversion['Version']['application_id']);
	        
	         $this->Maconnexion->ddlquery($sonapp,
	        	'ALTER TABLE `'.$satable['Table']['name'].'_$'.$satable['Table']['id'].'` '
	        	.'ADD COLUMN '.$this->request->data['Colonne']['name'].' '
	        	.' '.$colonne_type.' '.$colonne_notnull.' '
	        	.';'
	        );

	        if($this->request->data['Colonne']['topPk']=='1'){
	        	try {
				     $this->Maconnexion->ddlquery($sonapp,
		        	'DROP INDEX `CLE` ON `'.$satable['Table']['name'].'_$'.$satable['Table']['id'].'` ;'
		        	);
				} catch (Exception $e) {
				    $this->Session->setFlash(__('Error Drop PRIMARY KEY'),'default',array("class"=>'error'));
				}

				try {
				     $this->Maconnexion->ddlquery($sonapp,
		        	'CREATE INDEX `CLE` ON `'.$satable['Table']['name'].'_$'.$satable['Table']['id'].'` ('.$colonne_pk.');'
		        	);
				} catch (Exception $e) {
				    $this->Session->setFlash(__('Error Create PRIMARY KEY'),'default',array("class"=>'error'));
				}    
	        };
	    };
	    
	    //$this->Maconnexion->changer();
	}
/**
 * edit method
 *
 * @param string $id
 * @return void
 */
	public function edit($id = null) {
		$this->Colonne->id = $id;
		if (!$this->Colonne->exists()) {
			throw new NotFoundException(__('Invalid colonne'));
		}
		if ($this->request->is('post') || $this->request->is('put')) {
			//suppression de caractères spéciaux dans le nom de la Colonne
			$this->request->data['Colonne']['name'] = Sanitize::paranoid($this->request->data['Colonne']['name'],array('_', '-')); 
			//Mise en majuscule du nom de la Colonne
			$this->request->data['Colonne']['name'] = strtoupper($this->request->data['Colonne']['name']);
			
			//$this->request->data = Sanitize::clean($this->request->data, array('encode' => false, 'unicode'=> true));
			
			if ($this->Colonne->save($this->request->data)) {
				$this->Session->setFlash(__('The colonne has been saved'),'default',array("class"=>'success'));
				$this->redirect(array('action' => 'view',$id));
			} else {
				$this->Session->setFlash(__('The colonne could not be saved. Please, try again.'),'default',array("class"=>'error'));
			}
		} else {
			$this->request->data = $this->Colonne->read(null, $id);
		}
		$pgtypecols = $this->Colonne->Pgtypecol->find('list');
		$this->set(compact('pgtypecols'));
	}

/**
 * delete method
 *
 * @param string $id
 * @return void
 */
	public function delete($id = null, $recursive = null) {

		if (!$this->request->is('post') || ($id == null && ( !is_array($this->request->data['colonne']) || !isset($this->request->data['colonne'][0])))) {
			throw new MethodNotAllowedException();
		}

		if ($id != null ) {
			$this->Colonne->id = $id;
			if (!$this->Colonne->exists()) {
				throw new NotFoundException(__('Invalid colonne'));
			}

			//debug('lacolonne 1 '.$id);
			$lacolonne=$this->Colonne->find('first',array('conditions' => array('Colonne.id' => $id),'recursive'=>-1));
			//debug('lacolonne 2 '.$id);
			//$satable = $this->Colonne->Table->find('first',array('conditions' => array('Table.id' => $id),'recursive'=>-1));
			$idtable = $lacolonne['Colonne']['table_id'];
			//debug($lacolonne);
			if ($this->Colonne->delete()) {
				$this->delete_colonne_physique($id, $lacolonne['Colonne']['name'], $lacolonne['Colonne']['topPk'], $idtable);
				$this->Session->setFlash(__('The Colonne has been deleted'),'default',array("class"=>'success'));
				if ($recursive==null){
					$this->redirect(array('controller'=>'tables', 'action' => 'view',$idtable));
				}else{
					return true;
				}
			}
		}elseif (is_array($this->request->data['colonne'])){
			$lacolonne=$this->Colonne->find('first',array('conditions' => array('Colonne.id' => $this->request->data['colonne'][0]),'recursive'=>-1));
			$idtable = $lacolonne['Colonne']['table_id'];
			foreach ($this->request->data['colonne'] as $i){
				$this->delete($i, 'true');
			}
			$this->Session->setFlash(__('The Colonnes have been deleted'),'default',array("class"=>'success'));
			$this->redirect(array('controller'=>'tables', 'action' => 'view',$idtable));
		}
		$this->Session->setFlash(__('Colonne was not deleted'),'default',array("class"=>'error'));
		$this->redirect(array('controller'=>'tables', 'action' => 'view',$idtable));
	}

	public function delete_colonne_physique($idcolonne, $name_colonne, $topPk_colonne, $idtable){
		//debug('lacolonne 3.01');
		$satable=$this->Colonne->Table->find('first',array('conditions' => array('Table.id' => $idtable),'recursive'=>-1));
		//debug('lacolonne 3.02');
		$saversion=$this->Colonne->Table->Version->find('first',array('conditions' => array('Version.id' => $satable['Table']['version_id']),'recursive'=>-1));
		$sonapp = $this->Colonne->Table->Version->Application->find('first',array('conditions' => array('Application.id' => $saversion['Version']['application_id']),'recursive'=>-1));
		//debug('lacolonne 3.03');
		$colonne_pk=" Gestparamphp_VERSION";
        if($topPk_colonne=='1'){

        	$colonnes = $this->Colonne->find('all', array('conditions' => array('Colonne.table_id =' => $satable['Table']['id'],'Colonne.topPk =' => '1'),'recursive'=>-1 ));
        	
        	foreach ($colonnes as $colonne):
        		$colonne_pk = $colonne_pk.",".$colonne['Colonne']['name'];
			endforeach;

        };

        //$this->Maconnexion->changer($saversion['Version']['application_id']);

        if($topPk_colonne=='1'){
        	try {
			    $this->Maconnexion->ddlquery($sonapp,
	        	'ALTER TABLE `'.$satable['Table']['name'].'_$'.$satable['Table']['id'].'` '
	        	.'DROP PRIMARY KEY ;'
	        	);
			} catch (Exception $e) {
			    $this->Session->setFlash(__('Error Drop PRIMARY KEY'),'default',array("class"=>'error'));
			}
        };

        try {
        	$this->Maconnexion->ddlquery($sonapp,
	        	'ALTER TABLE `'.$satable['Table']['name'].'_$'.$satable['Table']['id'].'` '
	        	.'DROP COLUMN '.$name_colonne.' '
	        	.';'
	        );
		} catch (Exception $e) {
		    $this->Session->setFlash(__('Error Create PRIMARY KEY'),'default',array("class"=>'error'));
		}

        if($topPk_colonne=='1'){
			try {
			    $this->Maconnexion->ddlquery($sonapp,
	        	'ALTER TABLE `'.$satable['Table']['name'].'_$'.$satable['Table']['id'].'` '
	        	.'ADD PRIMARY KEY ('.$colonne_pk.')'
	        	.';'
	        	);
			} catch (Exception $e) {
			    $this->Session->setFlash(__('Error Create PRIMARY KEY'),'default',array("class"=>'error'));
			}
        };
	}

}